﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Text;
using System.Windows.Forms;
using WeifenLuo.WinFormsUI.Docking;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

namespace LCWMS
{
    public partial class w_rpt009 : DockContent
    {
        public w_rpt009()
        {
            InitializeComponent();
        }
        private DataSet dscustomer;
        private ReportDocument rd_report = new ReportDocument();
        private string g_str_asntype = "";
        private void w_rpt009_Load(object sender, EventArgs e)
        {
            string str_sql = "", str_asnname = "";
            DataSet ds_asnname, ds_supply;
            //日期控件默认从今天到一个星期
            //dtp_start.CustomFormat = "yyyy.MM.dd HH:mm:ss";
            dtp_start.CustomFormat = "yyyy.MM.dd";
            dtp_start.Format = DateTimePickerFormat.Custom;
            dtp_end.CustomFormat = "yyyy.MM.dd";
            dtp_end.Format = DateTimePickerFormat.Custom;
            dtp_start.Value = System.Convert.ToDateTime(DateTime.Now.ToString("yyyy.MM.dd").Trim());
            dtp_end.Value = System.Convert.ToDateTime(DateTime.Now.AddDays(7).ToString("yyyy.MM.dd").Trim());

            //初始化供应商
            try
            {
                if (main.dbtype == "SQL")
                {
                    str_sql = "select ltrim(rtrim(consignor)) consignor,ltrim(rtrim(consignor_name)) consignor_name from " + main.g_space + ".mst_customer where inv_org = '" + main.g_org + "' and supply_flag = '1' and main_dist <> 'D' order by consignor";
                    ds_supply = main.dbservice.Sql_exec_selectsql(str_sql);
                }
                else
                {
                    str_sql = "select trim(consignor) consignor,trim(consignor_name) consignor_name from " + main.g_space + ".mst_customer where inv_org = '" + main.g_org + "' and supply_flag = '1' and main_dist <> 'D' order by consignor";
                    ds_supply = main.dbservice.oracle_exec_selectsql(str_sql);
                }
                DataRow drnew;
                drnew = ds_supply.Tables[0].NewRow();
                drnew["consignor"] = "";
                drnew["consignor_name"] = "";
                ds_supply.Tables[0].Rows.Add(drnew);
                cbx_cuscode.ValueMember = "consignor";
                cbx_cuscode.DisplayMember = "consignor_name";
                cbx_cuscode.DataSource = ds_supply.Tables[0];
                cbx_cuscode.SelectedValue = "";
            }
            catch (System.Exception ee)
            {
                MessageBox.Show(main.g_class.comm_getchangestring("Fail to initialize customer's Information", main.g_info_dataset) + "!" + ee.ToString());
                return;
            }
            //初始化变量
            dscustomer = new DataSet();
            //初始化货主编码 
            try
            {
                dscustomer = main.g_class.comm_get_allconsignor();
                cbx_customer.ValueMember = "consignor";
                cbx_customer.DisplayMember = "consignor";
                DataRow drnew;
                drnew = dscustomer.Tables[0].NewRow();
                drnew["consignor"] = "";
                drnew["consignor_name"] = "";
                dscustomer.Tables[0].Rows.Add(drnew);
                cbx_customer.DataSource = dscustomer.Tables[0];
                if (dscustomer.Tables[0].Rows.Count > 0) tb_custname.Text = dscustomer.Tables[0].Rows[0][1].ToString().Trim();
            }
            catch (System.Exception ee)
            {
                MessageBox.Show(main.g_class.comm_getchangestring("Fail to get Consignor's Information", main.g_info_dataset) + "!" + ee.ToString());
                return;
            }
            //退货是否显示在入库明细报表中 类型1为不显示
            try
            {
                if (main.dbtype == "SQL")
                {
                    str_sql = "select isnull(id_data,'') from " + main.g_space + ".sys_parameter where inv_org='" + main.g_org + "'and id=8900";
                    g_str_asntype = main.dbservice.Sql_exec_selectstring(str_sql).Trim();
                }
                else
                {
                    str_sql = "select nvl(id_data,'') from " + main.g_space + ".sys_parameter where inv_org='" + main.g_org + "'and id=8900";
                    g_str_asntype = main.dbservice.oracle_exec_selectstring(str_sql).Trim();
                }
            }
            catch (System.Exception ee)
            {
                MessageBox.Show(ee.ToString());
            }


            //取报表表头名称
            if (main.dbtype == "SQL")
                str_sql = " select Ltrim(Rtrim(id_data)) as id_data from " + main.g_space + ".sys_parameter where inv_org = '" + main.g_org + "' and id =1001 ";
            else
                str_sql = " select trim(id_data) as id_data from " + main.g_space + ".sys_parameter where inv_org = '" + main.g_org + "' and id =1001 ";
            try
            {
                if (main.dbtype == "SQL")
                    ds_asnname = main.dbservice.Sql_exec_selectsql(str_sql);
                else
                    ds_asnname = main.dbservice.oracle_exec_selectsql(str_sql);
            }
            catch (System.Exception ee)
            {
                MessageBox.Show(main.g_class.comm_getchangestring("Fail to get Information of bill's detail", main.g_info_dataset) + "!" + ee.ToString());

                return;
            }
            if (ds_asnname.Tables[0].Rows.Count > 0)
            {
                str_asnname = ds_asnname.Tables[0].Rows[0][0].ToString().Trim();
            }

            //初始化之后，改变语言
            //语言转换
            main.g_class.comm_changeControl(this, main.g_win_dataset);

            if (main.g_report.Trim() == "")
            {
                rd_report.Load(main.g_path + "\\REPORT\\rpt009_rpt.rpt");
            }
            else
            {
                rd_report.Load(main.g_path + "\\" + main.g_report.Trim().ToUpper() + "_REPORT\\rpt009_rpt_" + main.g_report.Trim() + ".rpt");
            }          
            TextObject text;
            text = rd_report.ReportDefinition.ReportObjects["tb_asnname"] as TextObject;
            text.Text = str_asnname;

            text = rd_report.ReportDefinition.ReportObjects["tb_little"] as TextObject;
            text.Text = main.g_class.comm_getchangestring("Shipping Detail Report", main.g_info_dataset);

            text = rd_report.ReportDefinition.ReportObjects["line_no"] as TextObject;
            text.Text = main.g_class.comm_getchangestring("line_no", main.g_win_dataset);

            text = rd_report.ReportDefinition.ReportObjects["so_no"] as TextObject;
            text.Text = main.g_class.comm_getchangestring("so_no", main.g_win_dataset);

            text = rd_report.ReportDefinition.ReportObjects["eas_no"] as TextObject;
            text.Text = main.g_class.comm_getchangestring("eas_order", main.g_win_dataset);

            text = rd_report.ReportDefinition.ReportObjects["work_no"] as TextObject;
            text.Text = main.g_class.comm_getchangestring("work_no", main.g_win_dataset);


            text = rd_report.ReportDefinition.ReportObjects["so_day"] as TextObject;
            text.Text = main.g_class.comm_getchangestring("so_day", main.g_win_dataset);

            text = rd_report.ReportDefinition.ReportObjects["sku"] as TextObject;
            text.Text = main.g_class.comm_getchangestring("sku", main.g_win_dataset);

            text = rd_report.ReportDefinition.ReportObjects["sku_name"] as TextObject;
            text.Text = main.g_class.comm_getchangestring("sku_name", main.g_win_dataset);

            text = rd_report.ReportDefinition.ReportObjects["spec"] as TextObject;
            text.Text = main.g_class.comm_getchangestring("spec", main.g_win_dataset);

            text = rd_report.ReportDefinition.ReportObjects["type"] as TextObject;
            text.Text = main.g_class.comm_getchangestring("type", main.g_win_dataset);

            text = rd_report.ReportDefinition.ReportObjects["tax_dist"] as TextObject;
            text.Text = main.g_class.comm_getchangestring("tax_dist", main.g_win_dataset);

            text = rd_report.ReportDefinition.ReportObjects["ship_qty_m"] as TextObject;
            text.Text = main.g_class.comm_getchangestring("ship_qty_m", main.g_win_dataset);

            text = rd_report.ReportDefinition.ReportObjects["main_unit"] as TextObject;
            text.Text = main.g_class.comm_getchangestring("main_unit", main.g_win_dataset);

            text = rd_report.ReportDefinition.ReportObjects["ship_qty_s"] as TextObject;
            text.Text = main.g_class.comm_getchangestring("ship_qty_s", main.g_win_dataset);

            text = rd_report.ReportDefinition.ReportObjects["sub_unit"] as TextObject;
            text.Text = main.g_class.comm_getchangestring("sub_unit", main.g_win_dataset);

            text = rd_report.ReportDefinition.ReportObjects["plan_pick_qty_m"] as TextObject;
            text.Text = main.g_class.comm_getchangestring("plan_pick_qty_m", main.g_win_dataset);

            text = rd_report.ReportDefinition.ReportObjects["order_price"] as TextObject;
            text.Text = main.g_class.comm_getchangestring("order_price", main.g_win_dataset);

            text = rd_report.ReportDefinition.ReportObjects["warehouseman"] as TextObject;
            text.Text = main.g_class.comm_getchangestring("warehouseman", main.g_win_dataset);

            text = rd_report.ReportDefinition.ReportObjects["Batch"] as TextObject;
            text.Text = main.g_class.comm_getchangestring("Batch", main.g_win_dataset);

            text = rd_report.ReportDefinition.ReportObjects["sum"] as TextObject;
            text.Text = main.g_class.comm_getchangestring("sum", main.g_win_dataset);
            crystalReportViewer1.ReportSource = rd_report;

            if (main.g_query_flag == "1")
            {
               // button2.Visible = true;
            }
            else
            {
                tabPage2.Parent = null;
            }
            if (main.g_str_content2.Trim() == "1")
            {
                tb_preparer.Text = main.g_username;
            }
        }

        private void btn_Print_Click(object sender, EventArgs e)
        {
            this.crystalReportViewer1.PrintReport();
        }

        private void btn_query_Click(object sender, EventArgs e)
        {
            //根据条件查询出相应的数据 
            string str_common;
            DataSet ds_query;
            if (main.dbtype == "SQL")   
            {
                str_common = "select a.so_no,a.eas_no,a.customer_sale_no,b.ship_day so_day,b.sku,c.sku_name,c.spec,c.type,b.lot_attribute3,b.lot_attribute5,sum(b.ship_qty_m) ship_qty_m," +
                           " c.main_unit,sum(b.ship_qty_s) ship_qty_s,c.sub_unit, sum(d.plan_so_qty_m) plan_so_qty_m,(case isnull(h.in_tax,1) when 0 then 1 else round(1+h.in_tax/100,4) end ) in_tax," +
                           "(case when a.consignor in ('CSJ001') then  convert(Decimal,isnull((case isnull(b.lot_attribute7,'0') when '' then 0 else isnull(b.lot_attribute7,'0') end ),'0')) else b.order_price end) order_price" +
                         " ,case when a.consignor in ('CSJ001') then case isnull(h.in_tax,0)  when 0 then " +
                            " convert(Decimal,(case isnull(b.lot_attribute7,'0') when '' then 0 else isnull(b.lot_attribute7,'0') end )) else round(convert(Decimal,(case isnull(b.lot_attribute7,'0') " +
                        " when '' then 0 else isnull(b.lot_attribute7,'0') end ))/(1+h.in_tax/100),4) end else case isnull(h.in_tax,0)  when 0 then b.order_price else round(b.order_price/(1+h.in_tax/100),4) end end tax_price" +
                      " ,sum(case when a.consignor in ('CSJ001') then case isnull(h.in_tax,0)  when 0 then " +
                        " round (convert(Decimal,(case isnull(b.lot_attribute7,'0') when '' then 0 else isnull(b.lot_attribute7,'0') end ))*b.ship_qty_m,4) else round(round((convert(Decimal,(case isnull(b.lot_attribute7,'0') " +
                        " when '' then 0 else  isnull(b.lot_attribute7,'0') end ))/(1+h.in_tax/100)),4)*b.ship_qty_m,4) end else case isnull(h.in_tax,0)  when 0 then round(b.order_price*b.ship_qty_m,4) else round(round((b.order_price/(1+h.in_tax/100)),4)*b.ship_qty_m,4) end end) tax_money " +
                            ",c.warehouseman,a.standby_2,f.text1 " +                   //20240816 更改左连接写法
                         " from " + main.g_space + ".so_ship_header a " +
                           " inner join " + main.g_space +".so_pick_detail  b   on a.inv_org=b.inv_org  and a.consignor=b.consignor  and a.so_no=b.so_no "+
                           " inner join " + main.g_space +".mst_sku         c   on b.inv_org=c.inv_org  and b.consignor=c.consignor  and b.sku=c.sku "+
                           " inner join " + main.g_space +".so_ship_detail  d   on b.inv_org=d.inv_org  and b.consignor=d.consignor  and b.so_no=d.so_no           and b.so_line_no=d.so_line_no " + 
                           " left join "  + main.g_space +".mst_stat_class   f  on ltrim(rtrim(c.class_code1))=ltrim(rtrim(f.class_code)) "  +
                           " left join "  + main.g_space + ".asn_order_header h on b.inv_org=h.inv_org  and b.consignor=h.consignor  and b.lot_attribute4=h.asn_no and c.inv_org=f.inv_org " +
                        " where a.inv_org='" + main.g_org + "'" +
                           " and b.batchno <> '0' and b.pallet_no<>'0' and b.pcik_status >= '3' ";
                //str_common += "";
                // and f.class_flag=* 'C1'
            } 
            else
            {
                str_common = "select a.so_no,a.eas_no,a.customer_sale_no,b.ship_day so_day,b.sku,c.sku_name,c.spec,c.type,b.lot_attribute3,b.lot_attribute5,sum(b.ship_qty_m) ship_qty_m," +
                              " c.main_unit,sum(b.ship_qty_s) ship_qty_s,c.sub_unit, sum(d.plan_so_qty_m) plan_so_qty_m,(case nvl(h.in_tax,1) when 0 then 1 else round(1+h.in_tax/100,4) end) in_tax,(case when a.consignor in ('CSJ001') then  To_Number(nvl(trim(b.lot_attribute7),0)) else b.order_price end) order_price " +
                      ",case when a.consignor in ('CSJ001') then case nvl(h.in_tax,0)  when 0 then " +
                            " To_Number(case nvl(b.lot_attribute7,'0') when '' then 0 else To_Number(nvl(b.lot_attribute7,'0')) end ) " +
                            " else round(To_Number((case nvl(b.lot_attribute7,'0') when '' then 0 else To_Number(nvl(b.lot_attribute7,'0')) end ))/(1+h.in_tax/100),4) end " +
                            " else case nvl(h.in_tax,0)  when 0 then b.order_price else round(b.order_price/(1+h.in_tax/100),4) end end tax_price" +
                      " ,sum (case when a.consignor in ('CSJ001') then case nvl(h.in_tax,0)  when 0 then " +
                           " round(To_Number((case nvl(b.lot_attribute7,'0') when '' then 0 else To_Number(nvl(b.lot_attribute7,'0')) end ))*b.ship_qty_m,4) " + 
                           " else round(round((To_Number((case nvl(b.lot_attribute7,'0') when '' then 0 else To_Number(nvl(b.lot_attribute7,'0')) end ))/(1+h.in_tax/100)),4)*b.ship_qty_m,4) end " +
                           "else case nvl(h.in_tax,0)  when 0 then round(b.order_price*b.ship_qty_m,4) else round(round((b.order_price/(1+h.in_tax/100)),4)*b.ship_qty_m,4) end end) tax_money" +
                            ",c.warehouseman,a.standby_2,f.text1 " +
                              " from " + main.g_space + ".so_ship_header a," + main.g_space + ".so_pick_detail b," + main.g_space + ".mst_sku c," + main.g_space + ".so_ship_detail d," + main.g_space + ".asn_order_header h," + main.g_space + ".mst_stat_class f  where a.inv_org='" + main.g_org + "' and a.inv_org=b.inv_org and b.inv_org=c.inv_org " +
                              "  and a.consignor=b.consignor and b.consignor=c.consignor and a.so_no=b.so_no and b.sku=c.sku and b.batchno <> '0' and b.pallet_no<>'0' and b.pcik_status >= '3' ";
                str_common += " and b.inv_org=d.inv_org and b.consignor=d.consignor and b.so_no=d.so_no and b.so_line_no=d.so_line_no  and b.inv_org=h.inv_org(+) and b.consignor=h.consignor(+) and b.lot_attribute4=h.asn_no(+) and c.inv_org=f.inv_org(+) and trim(c.class_code1)=trim(f.class_code(+)) and f.class_flag(+)= 'C1' ";
            }

            if (main.dbtype == "SQL")
            {
                str_common = str_common + " and isnull(b.ship_qty_m,0) > 0 ";
            }
            else
            {
                str_common = str_common + " and nvl(b.ship_qty_m,0) > 0 ";
            }
            //判断是否有货主
            if (cbx_customer.Text.Trim() != "")
            {
                str_common = str_common + " and a.consignor = '" + cbx_customer.Text.Trim() + "'  ";
            }
            else //没有货主提取有权限的所有货主
            {
                str_common = str_common + " and a.consignor in (select a.consignor from " + main.g_space + ".mst_customer a," + main.g_space + ".sys_user b," + main.g_space + ".sys_user_customer c" +
                           " where b.inv_org = c.inv_org and b.user_id = c.user_id and c.inv_org = a.inv_org and c.consignor = a.consignor and b.inv_org = '" + main.g_org + "' and b.user_id = '" + main.g_user + "' and a.consignor_flag='1')  ";
            }
            //查找订单号
            if (tb_sono.Text.Trim() != "")
            {
                str_common += " and a.so_no = '" + tb_sono.Text.Trim() + "'";
            }
            //仓库管理员
            if (tb_warehouseman.Text.Trim() != "")
            {
                str_common += " and c.warehouseman = '" + tb_warehouseman.Text.Trim() + "'";
            }
            //商品分类
            if (tb_sku_class.Text.Trim() != "")
            {
                str_common += " and c.sku_class = '" + tb_sku_class.Text.Trim() + "'";
            }
            //商品编码
            if (tb_sku.Text.Trim() != "")
            {
                str_common += " and b.sku = '" + tb_sku.Text.Trim() + "'";
            }
            //商品名称
            if (tb_sku_name.Text.Trim() != "")
            {
                str_common += " and c.sku_name like '%" + tb_sku_name.Text.Trim() + "%'";
            }
            //商品规格
            if (tb_spec.Text.Trim() != "")
            {
                str_common += " and c.spec like '%" + tb_spec.Text.Trim() + "%'";
            }
            if (tb_Batch.Text.Trim() != "")
            {
                str_common += " and b.lot_attribute5 = '" + tb_Batch.Text.Trim() + "'";
            }
            if (tb_work_no.Text.Trim() != "")
            {
                str_common += " and a.customer_sale_no = '" + tb_work_no.Text.Trim() + "'";
            }
            if (tb_eas.Text.Trim() != "")
            {
                str_common += " and a.eas_no = '" + tb_eas.Text.Trim() + "'";
            }
            //线别
            if (tb_line_type.Text.Trim() != "")
            {
                str_common += " and a.line_type='" + tb_line_type.Text.Trim() + "' ";
            }
            //工位
            if (tb_work_position.Text.Trim() != "")
            {
                str_common += " and a.work_position='" + tb_work_position.Text.Trim() + "' ";
            }
            //机组SO
            if (tb_mechine_no.Text.Trim() != "")
            {
                str_common += " and a.mechine_no='" + tb_mechine_no.Text.Trim() + "' ";
            }
            //序列号
            if (tb_sequence_no.Text.Trim() != "")
            {
                str_common += " and a.sequence_no='" + tb_sequence_no.Text.Trim() + "' ";
            }
            if (tb_mechine_type.Text.Trim() != "")
            {
                str_common += " and a.mechine_type='" + tb_mechine_type.Text.Trim() + "' ";
            }
            if (tb_eas_no.Text.Trim() != "")
            {
                str_common += " and a.eas_no='" + tb_eas_no.Text.Trim() + "'";
            }
            if (tb_preparer.Text.Trim() != "")
            {
                str_common += " and a.preparer='" + tb_preparer.Text.Trim() + "'";
            }
            if (cb_tax_dist.Text.Trim() != "")
            {
                if (cb_tax_dist.Text.Trim() == "保税")
                {
                    str_common += " and b.lot_attribute3 = '保税'";
                }
                else if (cb_tax_dist.Text.Trim() == "非保税")
                {
                    str_common += " and b.lot_attribute3 = '非保税'";
                }
            }
            if (cbx_cuscode.Text.Trim() != "")
            {
                str_common += " and a.customer_code = '" + cbx_cuscode.SelectedValue.ToString().Trim() + "'";
            }
            //退库的不显示在出库报表中
            if (g_str_asntype.Trim() == "1")
            {
                str_common += " and a.so_type <> '1'";
            }
            //判断订单日期
            if (checkBox1.Checked)
            {
                str_common = str_common + " and b.ship_day between '" + dtp_start.Text.ToString().Trim() + "' and '" + dtp_end.Text.ToString().Trim() + "'  ";
            }

            //把 退货的显示在入库报表中
            if (g_str_asntype.Trim() == "1")
            {
                if (tb_sono.Text.Trim() == "" && tb_back.Text.Trim() != "")
                {
                    str_common = str_common + " and 1=2 ";
                }

                str_common = str_common + " group by h.in_tax,a.so_no,a.eas_no,c.type,a.customer_sale_no,b.ship_day,b.sku,c.sku_name,c.spec,b.lot_attribute3,b.lot_attribute5, c.main_unit,c.sub_unit,b.order_price,c.warehouseman,b.lot_attribute7,a.consignor,a.standby_2,f.text1 ";


                if (main.dbtype == "SQL")
                {
                    str_common = str_common + " union select a.asn_no so_no,a.EAS_ORDER eas_no,a.customer_order_no customer_sale_no,b.lot_attribute2 ship_day,b.sku,c.sku_name,c.spec,c.type,b.lot_attribute3 ,b.lot_attribute5," +
                       "-sum(b.receive_qty_m) ship_qty_m,c.main_unit,-sum(b.receive_qty_s) ship_qty_s,c.sub_unit,-sum(e.order_qty_m) plan_so_qty_m,isnull(a.in_tax,0) in_tax,(case when a.consignor in ('CSJ001') then  convert(Decimal,isnull((case isnull(b.lot_attribute7,'0') when '' then 0 else isnull(b.lot_attribute7,'0') end ),'0')) else b.order_price end) order_price" +
                            " ,case when a.consignor in ('CSJ001') then case isnull(a.in_tax,0)  when 0 then " +
                            " convert(Decimal,(case isnull(b.lot_attribute7,'0') when '' then 0 else isnull(b.lot_attribute7,'0') end )) else round(convert(Decimal,(case isnull(b.lot_attribute7,'0') " +
                        " when '' then 0 else isnull(b.lot_attribute7,'0') end ))/(1+a.in_tax/100),4) end else case isnull(a.in_tax,0)  when 0 then b.order_price else round(b.order_price/(1+a.in_tax/100),4) end end tax_price" +
                      " ,sum(case when a.consignor in ('CSJ001') then case isnull(a.in_tax,0)  when 0 then " +
                        " round (convert(Decimal,(case isnull(b.lot_attribute7,'0') when '' then 0 else isnull(b.lot_attribute7,'0') end ))*b.receive_qty_m,4) else round(round((convert(Decimal,(case isnull(b.lot_attribute7,'0') " +
                        " when '' then 0 else  isnull(b.lot_attribute7,'0') end ))/(1+a.in_tax/100)),4)*b.receive_qty_m,4) end else case isnull(a.in_tax,0)  when 0 then round(b.order_price*b.receive_qty_m,4) else round(round((b.order_price/(1+a.in_tax/100)),4)*b.receive_qty_m,4) end end) tax_money " +
                            ",c.warehouseman,'' standby_2,f.text1 " +
                        " from " + main.g_space + ".asn_order_header a " +           //20240816 更改左连接写法
                            " inner join " + main.g_space +".asn_receive_detail b on a.inv_org=b.inv_org and a.consignor=b.consignor and a.asn_no=b.asn_no "+ 
                            " inner join " + main.g_space +".mst_sku            c on b.inv_org=c.inv_org and b.consignor=c.consignor and b.sku=c.sku "+ 
                            " inner join " + main.g_space + ".asn_order_detail  e on b.inv_org=e.inv_org and b.consignor=e.consignor and b.asn_no=e.asn_no and b.asn_line_no=e.asn_line_no "+  
                            " left join "  + main.g_space +".mst_customer       d on a.inv_org=d.inv_org and rtrim(ltrim(a.supply))= rtrim(ltrim(d.consignor)) "+
                            " left join "  + main.g_space + ".mst_stat_class    f on c.inv_org=f.inv_org and ltrim(rtrim(c.class_code1))=ltrim(rtrim(f.class_code)) " +  
                       "where  a.inv_org='" + main.g_org + "' and a.arrive_status in ('1','2') and a.asn_type='4'";
                    str_common = str_common + " and isnull(b.receive_qty_m,0) + isnull(b.receive_present_qty_m,0) > 0   "; 
                }
                else
                {

                    str_common = str_common + " union select a.asn_no so_no,a.EAS_ORDER eas_no,a.customer_order_no customer_sale_no,b.lot_attribute2 ship_day,b.sku,c.sku_name,c.spec,c.type,b.lot_attribute3 ,b.lot_attribute5," +
                       "-sum(b.receive_qty_m) ship_qty_m,c.main_unit,-sum(b.receive_qty_s) ship_qty_s,c.sub_unit,-sum(e.order_qty_m) plan_so_qty_m, nvl(a.in_tax,0) in_tax,(case when a.consignor in ('CSJ001') then  To_Number(nvl(trim(b.lot_attribute7),0)) else b.order_price end) order_price" +
                           ",case when a.consignor in ('CSJ001') then case nvl(a.in_tax,0)  when 0 then " +
                            " To_Number(case nvl(b.lot_attribute7,'0') when '' then 0 else To_Number(nvl(b.lot_attribute7,'0')) end ) " +
                            " else round(To_Number((case nvl(b.lot_attribute7,'0') when '' then 0 else To_Number(nvl(b.lot_attribute7,'0')) end ))/(1+a.in_tax/100),4) end " +
                            " else case nvl(a.in_tax,0)  when 0 then b.order_price else round(b.order_price/(1+a.in_tax/100),4) end end tax_price" +
                      " , sum(case when a.consignor in ('CSJ001') then case nvl(a.in_tax,0)  when 0 then " +
                           " round(To_Number((case nvl(b.lot_attribute7,'0') when '' then 0 else To_Number(nvl(b.lot_attribute7,'0')) end ))*b.receive_qty_m,4) " +
                           " else round(round((To_Number((case nvl(b.lot_attribute7,'0') when '' then 0 else To_Number(nvl(b.lot_attribute7,'0')) end ))/(1+a.in_tax/100)),4)*b.receive_qty_m,4) end " +
                           "else case nvl(a.in_tax,0)  when 0 then round(b.order_price*b.receive_qty_m,4) else round(round((b.order_price/(1+a.in_tax/100)),4)*b.receive_qty_m,4) end end) tax_money" +
                            ",c.warehouseman ,'' standby_2,f.text1 " +
                        " from " + main.g_space + ".asn_order_header a," + main.g_space + ".asn_receive_detail b," + main.g_space + ".mst_sku c," + main.g_space + ".mst_customer d ," + main.g_space + ".asn_order_detail e," + main.g_space + ".mst_stat_class f " +
                       "where a.inv_org=b.inv_org and b.inv_org=c.inv_org and a.inv_org='" + main.g_org + "' and a.consignor=b.consignor and b.consignor=c.consignor" +
                       " and b.inv_org=e.inv_org and b.consignor=e.consignor and b.asn_no=e.asn_no and b.asn_line_no=e.asn_line_no " +
                       " and a.asn_no=b.asn_no and b.sku=c.sku and a.arrive_status in ('1','2') and a.asn_type='4'";
                    str_common = str_common + " and nvl(b.receive_qty_m,0) + nvl(b.receive_present_qty_m,0) > 0 and a.inv_org=d.inv_org (+) and trim(a.supply)=trim(d.consignor (+)) and c.inv_org=f.inv_org(+) and trim(c.class_code1)=trim(f.class_code(+)) and f.class_flag(+)= 'C1' ";
                }
                str_common = str_common + " and b.lot_attribute2 between '" + dtp_start.Text.ToString().Trim() + "' and '" + dtp_end.Text.ToString().Trim() + "'  ";
                //判断是否有货主
                if (cbx_customer.Text.Trim() != "")
                {
                    str_common = str_common + " and a.consignor = '" + cbx_customer.Text.Trim() + "'  ";
                }
                else //没有货主提取有权限的所有货主 
                {
                    str_common = str_common + " and a.consignor in (select a.consignor from " + main.g_space + ".mst_customer a," + main.g_space + ".sys_user b," + main.g_space + ".sys_user_customer c" +
                               " where b.inv_org = c.inv_org and b.user_id = c.user_id and c.inv_org = a.inv_org and c.consignor = a.consignor and b.inv_org = '" + main.g_org + "' and b.user_id = '" + main.g_user + "' and a.consignor_flag='1')  ";
                }
                if (tb_back.Text.Trim() != "")
                {
                    str_common = str_common + " and a.asn_no = '" + tb_back.Text.Trim() + "'";
                }
                //仓库管理员
                if (tb_warehouseman.Text.Trim() != "")
                {
                    str_common += " and c.warehouseman = '" + tb_warehouseman.Text.Trim() + "'";
                }
                //商品分类
                if (tb_sku_class.Text.Trim() != "")
                {
                    str_common += " and c.sku_class = '" + tb_sku_class.Text.Trim() + "'";
                }
                //商品编码
                if (tb_sku.Text.Trim() != "")
                {
                    str_common += " and b.sku = '" + tb_sku.Text.Trim() + "'";
                }
                //商品名称
                if (tb_sku_name.Text.Trim() != "")
                {
                    str_common += " and c.sku_name like '%" + tb_sku_name.Text.Trim() + "%'";
                }
                //商品规格
                if (tb_spec.Text.Trim() != "")
                {
                    str_common += " and c.spec like '%" + tb_spec.Text.Trim() + "%'";
                }
                if (tb_Batch.Text.Trim() != "")
                {
                    str_common += " and b.lot_attribute5 = '" + tb_Batch.Text.Trim() + "'";
                }
                if (tb_work_no.Text.Trim() != "")
                {
                    str_common += " and a.customer_order_no = '" + tb_work_no.Text.Trim() + "'";
                }
                if (tb_eas.Text.Trim() != "")
                {
                    str_common += " and a.eas_order = '" + tb_eas.Text.Trim() + "'";
                }
                if (cb_tax_dist.Text.Trim() != "")
                {
                    if (cb_tax_dist.Text.Trim() == "保税")
                    {
                        str_common += " and b.lot_attribute3 = '保税'";
                    }
                    else if (cb_tax_dist.Text.Trim() == "非保税")
                    {
                        str_common += " and b.lot_attribute3 = '非保税'";
                    }
                }
                if (cbx_cuscode.Text.Trim() != "")
                {
                    str_common += " and a.supply like '%" + cbx_cuscode.SelectedValue.ToString().Trim() + "%'";
                }
                if (tb_sono.Text.Trim() != "" && tb_back.Text.Trim() == "")
                {
                    str_common = str_common + " and 1=2 ";
                }
                str_common += " group by a.consignor,b.lot_attribute7,a.in_tax,a.asn_no,a.EAS_ORDER,c.type,a.customer_order_no,b.lot_attribute2,b.sku,c.sku_name,c.spec,b.lot_attribute3 ,b.lot_attribute5," +
                         "c.main_unit,c.sub_unit,b.order_price,c.warehouseman,f.text1 ";
            }
            else
            {
                str_common = str_common + "  group by in_tax,a.so_no,a.eas_no,c.type,a.customer_sale_no,b.ship_day,b.sku,c.sku_name,c.spec,b.lot_attribute3,b.lot_attribute5," +
                       " c.main_unit,c.sub_unit,b.order_price,c.warehouseman,b.lot_attribute7,a.consignor,a.standby_2,f.text1 ";
            }
            if (main.dbtype == "SQL")
            {
                str_common += "union select a.tk_no so_no,'' eas_no,a.customer_sale_no," +
                               " a.confirm_day ship_day,b.sku,c.sku_name,c.spec,c.type,b.lot_attribute3,b.lot_attribute5,b.confirm_qty_m ship_qty_m,c.main_unit," +
                               " b.confirm_qty_s ship_qty_s,c.sub_unit,b.confirm_qty_m plan_so_qty_m,isnull(h.in_tax,0) in_tax,(case when a.consignor in ('CSJ001') then  convert(Decimal,isnull((case isnull(b.lot_attribute7,'0') when '' then 0 else isnull(b.lot_attribute7,'0') end ),'0')) else b.order_price end) order_price " +
                                " ,case when a.consignor in ('CSJ001') then case isnull(h.in_tax,0)  when 0 then " +
                            " convert(Decimal,(case isnull(b.lot_attribute7,'0') when '' then 0 else isnull(b.lot_attribute7,'0') end )) else round(convert(Decimal,(case isnull(b.lot_attribute7,'0') " +
                        " when '' then 0 else isnull(b.lot_attribute7,'0') end ))/(1+h.in_tax/100),4) end else case isnull(h.in_tax,0)  when 0 then b.order_price else round(b.order_price/(1+h.in_tax/100),4) end end tax_price" +
                      " ,(case when a.consignor in ('CSJ001') then case isnull(h.in_tax,0)  when 0 then " +
                        " round (convert(Decimal,(case isnull(b.lot_attribute7,'0') when '' then 0 else isnull(b.lot_attribute7,'0') end ))*b.confirm_qty_m,4) else round(round((convert(Decimal,(case isnull(b.lot_attribute7,'0') " +
                        " when '' then 0 else  isnull(b.lot_attribute7,'0') end ))/(1+h.in_tax/100)),4)*b.confirm_qty_m,4) end else case isnull(h.in_tax,0)  when 0 then round(b.order_price*b.confirm_qty_m,4) else round(round((b.order_price/(1+h.in_tax/100)),4)*b.confirm_qty_m,4) end end) tax_money " +
                            ",c.warehouseman,a.customer_name standby_2,f.text1 " +
                                " from  " + main.g_space + ".so_temp_header a" +   //20240816 更改左连接写法
                                " inner join " + main.g_space +".so_temp_detail   b on a.inv_org=b.inv_org and a.consignor=b.consignor  and a.tk_no=b.tk_no "+
                                " inner join " + main.g_space +".mst_sku          c on b.inv_org=c.inv_org and b.consignor=c.consignor  and b.sku=c.sku "+ 
                                " left join  " + main.g_space +".asn_order_header h on b.inv_org=h.inv_org and b.consignor=h.consignor and b.lot_attribute4=h.asn_no "+
                                " left join  " + main.g_space + ".mst_stat_class  f on c.inv_org=f.inv_org and ltrim(rtrim(c.class_code1))=ltrim(rtrim(f.class_code)) where a.inv_org='" + main.g_org + "'"; 
            }
            else
            {

                str_common += "union select a.tk_no so_no,'' eas_no,a.customer_sale_no," +
                                  " a.confirm_day ship_day,b.sku,c.sku_name,c.spec,c.type,b.lot_attribute3,b.lot_attribute5,b.confirm_qty_m ship_qty_m,c.main_unit," +
                                  " b.confirm_qty_s ship_qty_s,c.sub_unit,b.confirm_qty_m plan_so_qty_m,nvl(h.in_tax,0) in_tax,(case when a.consignor in ('CSJ001') then  To_Number(nvl(trim(b.lot_attribute7),0)) else b.order_price end) order_price " +
                        ",case when a.consignor in ('CSJ001') then case nvl(h.in_tax,0)  when 0 then " +
                            " To_Number(case nvl(b.lot_attribute7,'0') when '' then 0 else To_Number(nvl(b.lot_attribute7,'0')) end ) " +
                            " else round(To_Number((case nvl(b.lot_attribute7,'0') when '' then 0 else To_Number(nvl(b.lot_attribute7,'0')) end ))/(1+h.in_tax/100),4) end " +
                            " else case nvl(h.in_tax,0)  when 0 then b.order_price else round(b.order_price/(1+h.in_tax/100),4) end end tax_price" +
                      " , (case when a.consignor in ('CSJ001') then case nvl(h.in_tax,0)  when 0 then " +
                           " round(To_Number((case nvl(b.lot_attribute7,'0') when '' then 0 else To_Number(nvl(b.lot_attribute7,'0')) end ))*b.confirm_qty_m,4) " +
                           " else round(round((To_Number((case nvl(b.lot_attribute7,'0') when '' then 0 else To_Number(nvl(b.lot_attribute7,'0')) end ))/(1+h.in_tax/100)),4)*b.confirm_qty_m,4) end " +
                           "else case nvl(h.in_tax,0)  when 0 then round(b.order_price*b.confirm_qty_m,4) else round(round((b.order_price/(1+h.in_tax/100)),4)*b.confirm_qty_m,4) end end) tax_money" +
                            ",c.warehouseman ,a.customer_name standby_2,f.text1 " +
                                   " from  " + main.g_space + ".so_temp_header a," + main.g_space + ".so_temp_detail b," + main.g_space + ".mst_sku c," + main.g_space + ".asn_order_header h," + main.g_space + ".mst_stat_class f  where a.inv_org='" + main.g_org + "' and a.inv_org=b.inv_org and a.consignor=b.consignor and a.tk_no=b.tk_no " +
                                   " and b.inv_org=c.inv_org and b.consignor=c.consignor  and b.sku=c.sku and b.inv_org=h.inv_org(+) and b.consignor=h.consignor(+) and b.lot_attribute4=h.asn_no(+) and c.inv_org=f.inv_org(+) and trim(c.class_code1)=trim(f.class_code(+)) and f.class_flag(+)= 'C1' ";
            }
            //判断是否有货主
            if (cbx_customer.Text.Trim() != "")
            {
                str_common = str_common + " and a.consignor = '" + cbx_customer.Text.Trim() + "'  ";
            }
            else //没有货主提取有权限的所有货主
            {
                str_common = str_common + " and a.consignor in (select a.consignor from " + main.g_space + ".mst_customer a," + main.g_space + ".sys_user b," + main.g_space + ".sys_user_customer c" +
                           " where b.inv_org = c.inv_org and b.user_id = c.user_id and c.inv_org = a.inv_org and c.consignor = a.consignor and b.inv_org = '" + main.g_org + "' and b.user_id = '" + main.g_user + "' and a.consignor_flag='1')  ";
            }
            //查找订单号
            if (tb_sono.Text.Trim() != "")
            {
                str_common += " and a.tk_no = '" + tb_sono.Text.Trim() + "'";
            }
            //仓库管理员
            if (tb_warehouseman.Text.Trim() != "")
            {
                str_common += " and c.warehouseman = '" + tb_warehouseman.Text.Trim() + "'";
            }
            //商品分类
            if (tb_sku_class.Text.Trim() != "")
            {
                str_common += " and c.sku_class = '" + tb_sku_class.Text.Trim() + "'";
            }
            //商品编码 
            if (tb_sku.Text.Trim() != "")
            {
                str_common += " and b.sku = '" + tb_sku.Text.Trim() + "'";
            }
            //商品名称
            if (tb_sku_name.Text.Trim() != "")
            {
                str_common += " and c.sku_name like '%" + tb_sku_name.Text.Trim() + "%'";
            }
            //商品规格
            if (tb_spec.Text.Trim() != "")
            {
                str_common += " and c.spec like '%" + tb_spec.Text.Trim() + "%'";
            }
            if (tb_Batch.Text.Trim() != "")
            {
                str_common += " and b.lot_attribute5 = '" + tb_Batch.Text.Trim() + "'";
            }
            if (tb_work_no.Text.Trim() != "")
            {
                str_common += " and a.customer_sale_no = '" + tb_work_no.Text.Trim() + "'";
            }

            if (cb_tax_dist.Text.Trim() != "")
            {
                if (cb_tax_dist.Text.Trim() == "保税")
                {
                    str_common += " and b.lot_attribute3 = '保税'";
                }
                else if (cb_tax_dist.Text.Trim() == "非保税")
                {
                    str_common += " and b.lot_attribute3 = '非保税'";
                }
            }
            if (cbx_cuscode.Text.Trim() != "")
            {
                str_common += " and a.customer_code = '" + cbx_cuscode.SelectedValue.ToString().Trim() + "'";
            }

            //判断订单日期
            str_common = str_common + " and a.confirm_day between '" + dtp_start.Text.ToString().Trim() + "' and '" + dtp_end.Text.ToString().Trim() + "'  ";
            if (main.dbtype == "SQL")
            {
                str_common = str_common + "  order by  a.so_no ";
            }
            else
            {
                str_common = str_common + "  order by  so_no ";
            }
            //提取数据
            try
            {
                if (main.dbtype == "SQL")
                    ds_query = main.dbservice.Sql_exec_selectsql(str_common);
                else
                    ds_query = main.dbservice.oracle_exec_selectsql(str_common);
                //加载数据  
                this.rd_report.SetDataSource(ds_query.Tables[0]);
                dgv_list.DataSource = ds_query.Tables[0];
                //   this.crv_asn.Refresh();
                crystalReportViewer1.ReportSource = rd_report;
            }
            catch (System.Exception ee)
            {
                MessageBox.Show(main.g_class.comm_getchangestring("Fail to get bill's Information", main.g_info_dataset) + "!" + ee.ToString());

            }
        }

        private void btn_Execl_Click(object sender, EventArgs e)
        {
            main.g_class.DataGridViewToExcel(dgv_list);
        }

        private void cbx_customer_SelectedIndexChanged(object sender, EventArgs e)
        {
            string str_cust;
            str_cust = cbx_customer.Text.Trim();
            tb_custname.Text = main.g_class.comm_findname(str_cust, dscustomer);
        }

        private void cbx_customer_TextChanged(object sender, EventArgs e)
        {
            string str_cust;
            str_cust = cbx_customer.Text.Trim();
            tb_custname.Text = main.g_class.comm_findname(str_cust, dscustomer);
        }

    }
}